const pool = require('../config/db');

/**
 * @desc    获取所有学生
 * @route   GET /api/students
 * @access  Private
 */
const getStudents = async (req, res) => {
    try {
    const [students] = await pool.query(`
        SELECT s.*, c.name as class_name 
        FROM students s
        LEFT JOIN classes c ON s.class_id = c.id
        ORDER BY s.student_id
    `);
    res.json(students);
    } catch (error) {
    console.error('获取学生列表错误:', error);
    res.status(500).json({ message: '服务器错误' });
    }
};

/**
 * @desc    获取单个学生
 * @route   GET /api/students/:id
 * @access  Private
 */
const getStudentById = async (req, res) => {
    try {
    const [students] = await pool.query(`
        SELECT s.*, c.name as class_name 
        FROM students s
        LEFT JOIN classes c ON s.class_id = c.id
        WHERE s.id = ?
    `, [req.params.id]);

    if (students.length === 0) {
        return res.status(404).json({ message: '学生不存在' });
    }

    res.json(students[0]);
    } catch (error) {
    console.error('获取学生信息错误:', error);
    res.status(500).json({ message: '服务器错误' });
    }
};

/**
 * @desc    创建学生
 * @route   POST /api/students
 * @access  Private
 */
const createStudent = async (req, res) => {
    try {
    const {
        student_id,
        name,
        gender,
        height,
        weight,
        birth_date,
        phone,
        nickname,
        class_id
    } = req.body;

    // 验证必填字段
    if (!student_id || !name || !gender || !phone) {
        return res.status(400).json({ message: '学号、姓名、性别和手机号为必填项' });
    }

    // 检查学号是否已存在
    const [existingStudents] = await pool.query(
        'SELECT * FROM students WHERE student_id = ?',
        [student_id]
    );

    if (existingStudents.length > 0) {
        return res.status(400).json({ message: '该学号已存在' });
    }

    // 检查班级是否存在
    if (class_id) {
        const [classes] = await pool.query(
        'SELECT * FROM classes WHERE id = ?',
        [class_id]
        );
        if (classes.length === 0) {
        return res.status(400).json({ message: '指定的班级不存在' });
        }
    }

    // 创建学生
    const [result] = await pool.query(`
        INSERT INTO students 
        (student_id, name, gender, height, weight, birth_date, phone, nickname, class_id)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `, [
        student_id,
        name,
        gender,
        height || null,
        weight || null,
        birth_date || null,
        phone,
        nickname || null,
        class_id || null
    ]);

    // 获取创建的学生
    const [newStudent] = await pool.query(
        'SELECT * FROM students WHERE id = ?',
        [result.insertId]
    );

    res.status(201).json({
        message: '学生创建成功',
        student: newStudent[0]
    });
    } catch (error) {
    console.error('创建学生错误:', error);
    res.status(500).json({ message: '服务器错误' });
    }
};

/**
 * @desc    更新学生
 * @route   PUT /api/students/:id
 * @access  Private
 */
const updateStudent = async (req, res) => {
    try {
    const {
        student_id,
        name,
        gender,
        height,
        weight,
        birth_date,
        phone,
        nickname,
        class_id
    } = req.body;

    // 检查学生是否存在
    const [students] = await pool.query(
        'SELECT * FROM students WHERE id = ?',
        [req.params.id]
    );

    if (students.length === 0) {
        return res.status(404).json({ message: '学生不存在' });
    }

    // 验证必填字段
    if (!student_id || !name || !gender || !phone) {
        return res.status(400).json({ message: '学号、姓名、性别和手机号为必填项' });
    }

    // 检查学号是否已被其他学生使用
    if (student_id !== students[0].student_id) {
        const [existingStudents] = await pool.query(
        'SELECT * FROM students WHERE student_id = ? AND id != ?',
        [student_id, req.params.id]
        );

        if (existingStudents.length > 0) {
        return res.status(400).json({ message: '该学号已存在' });
        }
    }

    // 检查班级是否存在
    if (class_id) {
        const [classes] = await pool.query(
        'SELECT * FROM classes WHERE id = ?',
        [class_id]
        );
        if (classes.length === 0) {
        return res.status(400).json({ message: '指定的班级不存在' });
        }
    }

    // 更新学生
    await pool.query(`
        UPDATE students SET
        student_id = ?,
        name = ?,
        gender = ?,
        height = ?,
        weight = ?,
        birth_date = ?,
        phone = ?,
        nickname = ?,
        class_id = ?
        WHERE id = ?
    `, [
        student_id,
        name,
        gender,
        height || null,
        weight || null,
        birth_date || null,
        phone,
        nickname || null,
        class_id || null,
        req.params.id
    ]);

    // 获取更新后的学生
    const [updatedStudent] = await pool.query(
        'SELECT * FROM students WHERE id = ?',
        [req.params.id]
    );

    res.json({
        message: '学生更新成功',
        student: updatedStudent[0]
    });
    } catch (error) {
    console.error('更新学生错误:', error);
    res.status(500).json({ message: '服务器错误' });
    }
};

/**
 * @desc    删除学生
 * @route   DELETE /api/students/:id
 * @access  Private
 */
const deleteStudent = async (req, res) => {
    try {
    // 检查学生是否存在
    const [students] = await pool.query(
        'SELECT * FROM students WHERE id = ?',
        [req.params.id]
    );

    if (students.length === 0) {
        return res.status(404).json({ message: '学生不存在' });
    }

    // 删除学生
    await pool.query(
        'DELETE FROM students WHERE id = ?',
        [req.params.id]
    );

    res.json({ message: '学生删除成功' });
    } catch (error) {
    console.error('删除学生错误:', error);
    res.status(500).json({ message: '服务器错误' });
    }
};

module.exports = {
    getStudents,
    getStudentById,
    createStudent,
    updateStudent,
    deleteStudent
};

